import pandas as pd

# Load the CSV file into a DataFrame
file_path = '/mnt/data/西门代送_20241020183034.csv'
df = pd.read_csv(file_path)

# Step 1: Extract relevant columns
columns_to_keep = ["提交时间", "手机号码", "配送地点", "应付金额", "序号"]
columns_to_merge_business = [col for col in df.columns if "选择商家" in col]
columns_to_merge_time = [col for col in df.columns if "选择配送时间点" in col]
columns_to_merge_dishes = [col for col in df.columns if "选择餐品" in col]

# Create new columns by merging relevant columns
df["选择商家"] = df[columns_to_merge_business].bfill(axis=1).iloc[:, 0]
df["配送时间"] = df[columns_to_merge_time].bfill(axis=1).iloc[:, 0]
df["选择餐品"] = df[columns_to_merge_dishes].bfill(axis=1).iloc[:, 0]

# Step 2: Create a new column "手机尾号" containing the last 6 digits of the phone number
df["手机尾号"] = df["手机号码"].astype(str).str[-6:]

# Step 3: Keep only the specified columns and the new ones
final_columns = columns_to_keep + ["选择商家", "配送时间", "选择餐品", "手机尾号"]
df_final = df[final_columns]

import ace_tools as tools
tools.display_dataframe_to_user(name="Processed Table", dataframe=df_final)
